<html>
<body>
	<table border="1">
		<tr>
			<td valign="top">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="index.html" />
			</td>
			<td><%@ page language="java" import="java.sql.*,java.util.*"%>
				<%
					Connection conn = null;
					try {
						// Load Oracle Driver class file
						DriverManager
								.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

						// Make a connection to the MS SQL database "cse132b"
						conn = DriverManager
								.getConnection(
										"jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=cse132b",
										"cse132b", "cse132b");

						String action = request.getParameter("action");
						// Check if an insertion is requested
						if (action != null && action.equals("insert")) {
							// Begin transaction
							conn.setAutoCommit(false);
							
							PreparedStatement pstmt = conn.prepareStatement(
					                "INSERT INTO degree_requirement VALUES (?, ?, ?)");
							
							pstmt.setString(1, request.getParameter("title"));     
							pstmt.setString(2, request.getParameter("department")); 
							pstmt.setInt(3, Integer.parseInt(request.getParameter("min_units")));  
							
					        pstmt.executeUpdate();				
							// Commit transaction
							conn.commit();
							conn.setAutoCommit(true);
						}
						
						// Check if an update is requested
						if (action != null && action.equals("update")) {
							// Begin transaction
	                        conn.setAutoCommit(false);
	                        
	                        // Create the prepared statement and use it to
	                        // UPDATE the course attributes in the course table.
	                        PreparedStatement pstmt = conn.prepareStatement(
	                            "UPDATE degree_requirement SET title = ?, department = ?, "+
	                            	"min_units = ? WHERE requirement_id = ?");
	                        
	                        pstmt.setString(1, request.getParameter("title"));     
							pstmt.setString(2, request.getParameter("department")); 
							pstmt.setInt(3, Integer.parseInt(request.getParameter("min_units")));  
							pstmt.setInt(4, Integer.parseInt(request.getParameter("requirement_id")));
	                       
	                        pstmt.executeUpdate();
	                              
	                        // Commit transaction
	                        conn.commit();
	                        conn.setAutoCommit(true);
						}
						
						if (action != null && action.equals("delete")) {
							// Begin transaction
	                        conn.setAutoCommit(false);
							// delete undergrad
	                        PreparedStatement pstmt = conn.prepareStatement(
		                            "SELECT category_id FROM category WHERE requirement_id= ?");            
	                        pstmt.setInt(1, Integer.parseInt(request.getParameter("requirement_id")));  
	                        ResultSet rs = pstmt.executeQuery();
	                        while(rs.next()){
	                        	pstmt = conn.prepareStatement("DELETE FROM category_course WHERE category_id= ?");
	                        	pstmt.setInt(1, rs.getInt("category_id"));
	                        	pstmt.executeUpdate();
	                        	pstmt = conn.prepareStatement("DELETE FROM category WHERE category_id= ?");
	                        	pstmt.setInt(1, rs.getInt("category_id"));
	                        	pstmt.executeUpdate();
	                        }
	                        
                        	// delete grad
                        	pstmt = conn.prepareStatement(
		                            "SELECT concentration_id FROM concentration WHERE requirement_id= ?");            
	                        pstmt.setInt(1, Integer.parseInt(request.getParameter("requirement_id")));  
	                        rs = pstmt.executeQuery();
	                        while(rs.next()){
	                        	pstmt = conn.prepareStatement("DELETE FROM concentration_course WHERE concentration_id= ?");
	                        	pstmt.setInt(1, rs.getInt("concentration_id"));
	                        	pstmt.executeUpdate();
	                        	pstmt = conn.prepareStatement("DELETE FROM concentration WHERE concentration_id= ?");
	                        	pstmt.setInt(1, rs.getInt("concentration_id"));
	                        	pstmt.executeUpdate();
	                        }
	                        pstmt = conn.prepareStatement("DELETE FROM degree_requirement WHERE requirement_id= ?");
                        	pstmt.setInt(1, Integer.parseInt(request.getParameter("requirement_id")));
                        	pstmt.executeUpdate();
                        	
                  
	                        // Commit transaction
	                        conn.commit();
	                        conn.setAutoCommit(true);
						}
					
						// Create the statement
						Statement statement = conn.createStatement();
						
						// SELECT the departments FROM the department table.
						ResultSet rsDept = statement.executeQuery("SELECT * FROM department");
						// Save departments for later use.
						List<String> departments = new ArrayList<String>();
						while (rsDept.next()) {
							departments.add(rsDept.getString("department"));
						}

						// Use the created statement to SELECT
						// the student attributes FROM the student table.
						ResultSet rs = statement.executeQuery("SELECT * FROM degree_requirement");
				%>
				<table border="1">
					<tr>
						<th>Title</th>
						<th>Department</th>	
						<th>Minimum Units</th>			
					</tr>
					<tr>
						<form action="degree_requirement.jsp" method="POST">
							<input type="hidden" value="insert" name="action">
							<th>
								<select name="title" style="width:100px">
									<option value="BA">BA</option>
									<option value="BSC">BSC</option>
									<option value="MA">MA</option>
									<option value="MS">MS</option>
									<option value="PhD">PhD</option>
								</select>	
							</th>
							<th>
							<select name="department" style="width:200px">
								<%
									for (String department : departments) {
								%>
									<option value="<%= department %>" >	<%= department %> </option>
								<% 
									}
								%>
								</select>
							</th>
                        	<th><input type="text" name="min_units" size="10"/>   </th>
							<th><input type="submit" value="Insert"></th>
						</form>
					</tr>
					<%
							// Iterate over the ResultSet
							while (rs.next()) {  
					%>
					<tr>
                        <form action="degree_requirement.jsp" method="POST">
                            <input type="hidden" value="update" name="action">
							<input type="hidden" value="<%= rs.getInt("requirement_id") %>" name="requirement_id">
                            <td> 
                            	<% String title = rs.getString("title"); %>
                                <select name="title" style="width:100px">
									<option value="BA" <% if (title.equals("BA")) out.print("selected='selected'"); %>>BA</option>
									<option value="BSC" <% if (title.equals("BSC")) out.print("selected='selected'"); %>>BSC</option>
									<option value="MA" <% if (title.equals("MA")) out.print("selected='selected'"); %>>MA</option>
									<option value="MS" <% if (title.equals("MS")) out.print("selected='selected'"); %>>MS</option>
									<option value="PhD" <% if (title.equals("PhD")) out.print("selected='selected'"); %>>PhD</option>
								</select>
                            </td>           
                            <td>
                            	<select name="department" style="width:200px">
								<%
									String dept = rs.getString("department");
									for (String department : departments) {
								%>
									<option value="<%= department %>" <% if (dept.equals(department)) out.print("selected='selected'"); %>>	<%= department %> </option>
								<% 
									}
								%>
								</select>
                            </td>
                            <td>
                            	<input value="<%= rs.getString("min_units") %>" name="min_units" size="10">
                            </td>
                            
                            <td>
                                <input type="submit" value="Update">
                            </td>
                        </form>
                        
                        <form action="degree_requirement.jsp" method="POST">
                            <input type="hidden" value="delete" name="action">
                            <input type="hidden" value="<%= rs.getInt("requirement_id") %>" name="requirement_id">
                            <td>
                                <input type="submit" value="Delete">
                            </td>
                        </form>
                    </tr>
					<%
							}
					%>
					<%
						} catch (SQLException e) {
							System.out.println(e.getMessage());
							conn.rollback();
						} catch (Exception e) {
							System.out.println(e.getMessage());
							conn.rollback();
						}
					%>
				</table>
			</td>
		</tr>
	</table>
</body>
</html>